package cn.iune.platform.dictionary;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;
import com.alibaba.fastjson.JSONObject;
import com.justep.baas.Utils;
import com.justep.baas.data.DataUtils;
import com.justep.baas.data.Table;
import com.justep.baas.data.Transform;

import cn.iune.common.base.BaseJPAImpl;

@Repository
public class RegionInfoDaoJPAImpl extends BaseJPAImpl<RegionInfo> implements IRegionInfoDao {

//	private final Logger log = LoggerFactory.getLogger(RegionInfoDaoJPAImpl.class);

	private String kSql;
	
//	@PersistenceContext 
//	private EntityManager em ;

	@Override
	public void insert(RegionInfo obj) {
		em.persist(obj);
	}

	@Override
	public void delete(String fID) {
		em.remove(em.getReference(RegionInfo.class, fID));
	}

	@Override
	public RegionInfo update(RegionInfo obj) {
		RegionInfo objNew = em.merge(obj);
		return objNew;
	}

	@Override
	public RegionInfo getByID(String fID) {
		RegionInfo singleResult = em.find(RegionInfo.class, fID);
		return singleResult;
	}

	@Override
	public List<RegionInfo> getAll() {
		Query query = em.createNamedQuery("RegionInfo.findAll");
		@SuppressWarnings("unchecked")
		List<RegionInfo> resultList = query.getResultList();
		return resultList;
	}
	
	@Override
	public RegionInfo getByName(String regionName, String nodeType) {
		// TODO Auto-generated method stub
		kSql="SELECT RegionInfo FROM RegionInfo RegionInfo "
				+ " WHERE ( RegionInfo.regionName = :regionName ) "
				+ " AND ( RegionInfo.nodeType = :nodeType ) "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("regionName", regionName);
		query.setParameter("nodeType", nodeType);
		RegionInfo resultSingle = null;
		try {
			resultSingle = (RegionInfo) query.getSingleResult();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultSingle;
	}
	
	@Override
	public List<RegionInfo> getRoots(){
		kSql="SELECT RegionInfo FROM RegionInfo RegionInfo "
				+ " WHERE RegionInfo.nodeType='REGION'"
				+ " ORDER BY RegionInfo.sortNo ASC"
				;
		Query query = em.createQuery(kSql);
		@SuppressWarnings("unchecked")
		List<RegionInfo> resultList = query.getResultList();
		return resultList;
		
	}
	
	@Override
	public List<RegionInfo> getProvinces(){
		kSql="SELECT RegionInfo FROM RegionInfo RegionInfo "
				+ " WHERE RegionInfo.nodeType='PROVINCE'"
				+ " ORDER BY RegionInfo.sortNo ASC"
				;
		Query query = em.createQuery(kSql);
		@SuppressWarnings("unchecked")
		List<RegionInfo> resultList = query.getResultList();
		return resultList;
		
	}
	
	@Override
	public List<RegionInfo> getChilds(String fParent) {
		// TODO Auto-generated method stub
		kSql="SELECT RegionInfo FROM RegionInfo RegionInfo "
				+ " WHERE ( RegionInfo.fParent = :fParent ) "
				+ " ORDER BY RegionInfo.sortNo ASC"
				;
		Query query = em.createQuery(kSql);
		query.setParameter("fParent", fParent);
		@SuppressWarnings("unchecked")
		List<RegionInfo> resultList = query.getResultList();
		return resultList;
	}
	
	@Override
	public List<RegionInfo> getChildsByFullName(String fullName, String nodeType) {
		// TODO Auto-generated method stub
		kSql="SELECT RegionInfo FROM RegionInfo RegionInfo "
//				+ " WHERE ( RegionInfo.regionFullName like CONCAT('%',:fullName,'%' ) "
				+ " WHERE ( RegionInfo.regionFullName like '%" + fullName + "%' ) "
				+ " AND ( RegionInfo.nodeType = :nodeType ) "
				+ " ORDER BY RegionInfo.sortNo ASC"
				;
		Query query = em.createQuery(kSql);
//		query.setParameter("fullName", fullName);
		query.setParameter("nodeType", nodeType);
		@SuppressWarnings("unchecked")
		List<RegionInfo> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<RegionInfo> getChildren(String rowID) {
		// TODO Auto-generated method stub
		kSql="SELECT RegionInfo FROM RegionInfo RegionInfo "
//				+ " WHERE ( RegionInfo.regionFullID like '%:rowID%' ) "
				+ " WHERE ( RegionInfo.regionFullID like '%" + rowID + "%' ) "
				+ " AND ( RegionInfo.fID <> '" + rowID + "' ) "
				+ " ORDER BY RegionInfo.sortNo DESC"
				;
		System.out.println(kSql);
		System.out.println(rowID);
		Query query = em.createQuery(kSql);
//		query.setParameter("rowID", rowID);
		@SuppressWarnings("unchecked")
		List<RegionInfo> resultList = query.getResultList();
		return resultList;
	}
	
	
	@Override
	public JSONObject create(JSONObject data) {
		//缺省列
		String defColstr = Utils.getDefCols(RegionInfo.class);
		data.put("defColstr", defColstr);
		return Utils.newData(data);
	}

	@Override
	public JSONObject query(JSONObject data) throws SQLException {
		
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fParent = data.getString("fParent");

		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		String select = "RegionInfo.*"
				//截取第2个‘/’之前的所有字符，然后再截取第1个‘/’之后的所有字符（从右向左数）
				+ ",substring_index(substring_index(RegionInfo.regionFullName,'/',2),'/',-1) as fRegion"
				+ ",substring_index(substring_index(RegionInfo.regionFullName,'/',3),'/',-1) as fProvince"
				+ ",substring_index(substring_index(RegionInfo.regionFullName,'/',4),'/',-1) as fCity"
				+ ",substring_index(RegionInfo.regionFullName,'/',-1) as fZone"
				;
		
		String from = "RegionInfo RegionInfo";
		
		String where = "";

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("regionCode LIKE ? OR regionName LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 2; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("fID='"+id+"'");
		}
		//处理fParent
		if (Utils.isNotEmptyString(fParent)) {
			filters.add("fParent='"+fParent+"'");
		}
	
//		System.out.println("fParent-->"+fParent);
//		System.out.println("filter-->"+filter);
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		JSONObject json = Transform.tableToJson(table);
		
		return json;
	}
		
}
